package cn.edu.sdjzu.xg.kcsj.dao;

import cn.edu.sdjzu.xg.kcsj.domain.DictItem;
import cn.edu.sdjzu.xg.kcsj.domain.Dictionary;
import cn.edu.sdjzu.xg.kcsj.service.DictionaryService;
import cn.edu.sdjzu.xg.kcsj.util.CommonDao;
import cn.edu.sdjzu.xg.kcsj.util.Condition;
import cn.edu.sdjzu.xg.kcsj.util.JdbcHelper;
import cn.edu.sdjzu.xg.kcsj.util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class DictItemDao {
    private static DictItemDao dictItemDao = new DictItemDao();
    private static Collection<Dictionary> dictionaries;
    private DictItemDao() {
    }

    public static DictItemDao getInstance() {
        return dictItemDao;
    }
    public Collection<DictItem> findAll(List<Condition> conditionList, Pagination pagination, Connection connection) throws SQLException {
        Collection<DictItem> dDictItems = new HashSet<DictItem>();
        StringBuilder sql_select = new StringBuilder(" select * from dict_item ");
        if (conditionList != null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        int totalNum = CommonDao.getInstance().count(connection,"dict_item",conditionList);
        String paginationClause = pagination.toLimitClause(totalNum);
        sql_select.append(paginationClause);
        PreparedStatement p1 = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = p1.executeQuery();
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String itemText = resultSet.getString("item_text");
            int dictId = resultSet.getInt("dict_id");
            Dictionary dictionary = DictionaryService.getInstance().find(dictId);
            DictItem dictItem = new DictItem(id,itemText,dictionary);
            dDictItems.add(dictItem);
        }
        JdbcHelper.close(resultSet,p1);
        return dDictItems;
    }
    public DictItem find(Integer id, Connection connection) throws SQLException{
        DictItem dictItem = null;
        String select_s = "select * from dict_item WHERE id=?";
        PreparedStatement p1 = connection.prepareStatement(select_s);
        p1.setInt(1,id);
        ResultSet resultSet = p1.executeQuery();
        if (resultSet.next()){
            String itemText = resultSet.getString("item_text");
            int dictId = resultSet.getInt("dict_id");
            Dictionary dictionary = DictionaryService.getInstance().find(dictId);
            dictItem = new DictItem(id,itemText,dictionary);
        }
        JdbcHelper.close(resultSet,p1);
        return dictItem;
    }
    public boolean delete(Integer id,Connection connection) throws SQLException {
        PreparedStatement p = connection.prepareStatement("DELETE FROM dict_item WHERE id=?");
        p.setInt(1,id);
        if (p.executeUpdate()==0){
            return false;
        }else {
            return true;
        }
    }
    public boolean update(DictItem dictItem,Connection connection) throws SQLException{
        String update_s = "UPDATE dict_item SET item_text=?,dict_id=? WHERE id=?";
        PreparedStatement p3 = connection.prepareStatement(update_s);
        p3.setString(1,dictItem.getItem_text() );
        p3.setInt(2, dictItem.getDictionary().getId());
        p3.setInt(3,dictItem.getId());
        if (p3.executeUpdate()==0){
            return false;
        }else {
            return true;
        }
    }

    public boolean add(DictItem dictItem,Connection connection) throws SQLException{
        String s_ins = "INSERT INTO dict_item" + " (item_text,dict_id) VALUES (?,?)";
        PreparedStatement p = connection.prepareStatement(s_ins);
        p.setString(1,dictItem.getItem_text());
        p.setInt(2,dictItem.getDictionary().getId());
        if (p.executeUpdate()==0){
            return false;
        }else {
            return true;
        }
    }

    public Collection<DictItem> findAllWithoutElse(Connection connection) throws SQLException {
        Collection<DictItem> dDictItems = new HashSet<DictItem>();
        String sql_select = "select * from dict_item";
        PreparedStatement p = connection.prepareStatement(sql_select);
        ResultSet resultSet = p.executeQuery();
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String itemText = resultSet.getString("item_text");
            int dictId = resultSet.getInt("dict_id");
            Dictionary dictionary = DictionaryService.getInstance().find(dictId);
            DictItem dictItem = new DictItem(id,itemText,dictionary);
            dDictItems.add(dictItem);
        }
        JdbcHelper.close(resultSet,p);
        return dDictItems;
    }
}
